﻿CREATE TABLE [dbo].[VersionInfo](
	[FarmId] [uniqueidentifier] NOT NULL,
	[Version] [tinyint] NOT NULL,
	[Created] [datetime] NOT NULL,
	[CreatedBy] [nvarchar](50) NOT NULL,
	[LicenseKey] [nvarchar](200) NULL,
 CONSTRAINT [PK_VersionInfo] PRIMARY KEY CLUSTERED 
(
	[FarmId], [Version] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetVersionInfo]
	@FarmId UniqueIdentifier
AS
BEGIN
	select top 1
		Version, Created, CreatedBy, LicenseKey 
	from
		VersionInfo
	where
		FarmId=@FarmId
	order by
		Version desc
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[SetVersionInfo]
	@Version tinyint,
	@FarmId UniqueIdentifier,
	@CreatedBy nvarchar(50),
	@LicenseKey nvarchar(200)
AS
BEGIN
if exists(select Version from VersionInfo where FarmId=@FarmId and Version=@Version)
begin
	update VersionInfo set
		CreatedBy=@CreatedBy,
		Created= getdate(),
		LicenseKey=@LicenseKey
	where
		FarmId = @FarmId
end
else
begin
	insert into VersionInfo
		(Version, Created, CreatedBy, FarmId, LicenseKey)
	values
		(@Version, getdate(), @CreatedBy, @FarmId, @LicenseKey)
end
END
GO
CREATE TABLE [dbo].[SiteInfo](
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[Title] [nvarchar](255) NOT NULL,
	[Url] [nvarchar](255) NOT NULL,
	[IndexDate] [datetime] NULL,
	[SiteCollectionTitle] [nvarchar](255) NOT NULL,
	[SiteCollectionUrl] [nvarchar](255) NOT NULL,
	[WebApplicationId] [uniqueidentifier] NOT NULL,
	[FarmId] [uniqueidentifier] NOT NULL
 CONSTRAINT [PK_SiteInfo] PRIMARY KEY CLUSTERED 
(
	[SiteID] ASC,
	[WebID] ASC
)WITH (PAD_INDEX  = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetSiteInfo]
	@FarmId UniqueIdentifier,
	@WebApplicationID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier
AS
BEGIN
	select
		Title, Url, SiteCollectionTitle, SiteCollectionUrl, IndexDate 
	from
		SiteInfo 
	where 
		FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[UpdateSiteInfo]
	@FarmId UniqueIdentifier,
	@WebApplicationID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier,
	@Title nvarchar(255),
	@Url nvarchar(255),
	@SiteCollectionTitle nvarchar(255),
	@SiteCollectionUrl nvarchar(255),
	@IndexDate DateTime
AS
BEGIN
if exists (select Title from SiteInfo where FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID)
begin
	update SiteInfo set Title = @Title, Url=@Url, SiteCollectionTitle=@SiteCollectionTitle, SiteCollectionUrl=@SiteCollectionUrl, IndexDate=@IndexDate 
	where FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID
end else
begin
	insert into SiteInfo 
		(FarmId, WebApplicationId, SiteID, WebID, Title, Url, SiteCollectionTitle, SiteCollectionUrl, IndexDate)
	values
		(@FarmId, @WebApplicationId, @SiteID, @WebID, @Title, @Url, @SiteCollectionTitle, @SiteCollectionUrl, @IndexDate)
end

END
GO
CREATE TABLE [dbo].[SiteObjects](
	[FarmID] [uniqueidentifier] NOT NULL,
	[WebApplicationID] [uniqueidentifier] NOT NULL,
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[ObjectID] [nvarchar](512) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[ObjectType] [tinyint] NOT NULL,
	[Url] [nvarchar](255) NULL,
	[Index] [bit] NOT NULL,
	[IndexScope] [tinyint] NOT NULL,
	[IndexByProperty] [tinyint] NOT NULL,
	[IndexItems] bit,
	[IndexID] [nvarchar](512) NOT NULL,
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[SiteObjects]  WITH NOCHECK ADD  CONSTRAINT [FK_SiteObjects_SiteInfo] FOREIGN KEY([SiteID], [WebID])
REFERENCES [dbo].[SiteInfo] ([SiteID], [WebID])
NOT FOR REPLICATION 
GO
ALTER TABLE [dbo].[SiteObjects] CHECK CONSTRAINT [FK_SiteObjects_SiteInfo]
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetSiteObjects]
	@FarmID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier,
	@ObjectType tinyint
AS
BEGIN
	select
		FarmID, WebApplicationID, SiteID, WebID, ObjectID, Name, ObjectType, Url, [Index], IndexScope, IndexByProperty, IndexItems, IndexId 
	from
		SiteObjects 
	where
		FarmID=@FarmID and SiteID=@SiteID and WebID=@WebID and ObjectType=@ObjectType
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[GetIndexObjects]
	@FarmID UniqueIdentifier
AS
BEGIN
	select
		FarmID, WebApplicationID, SiteID, WebID, ObjectID, Name, ObjectType, Url, [Index], IndexScope, IndexByProperty, IndexItems, IndexId 
	from
		SiteObjects 
	where
		FarmID=@FarmID and [Index]=1
END
GO
-- =============================================
CREATE PROCEDURE [dbo].[UpdateSiteObjects]
	@FarmId UniqueIdentifier,
	@WebApplicationID UniqueIdentifier,
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier,
	@ObjectType tinyint,
	@ObjectID nvarchar(512),
	@Name nvarchar(255),
	@Url nvarchar(255),
	@Index bit,
	@IndexScope tinyint,
	@IndexByProperty tinyint,
	@IndexItems bit,
	@IndexId nvarchar(512)
AS
BEGIN
if exists (select ObjectID from SiteObjects where FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID and ObjectType=@ObjectType and ObjectID=@ObjectID)
begin
	update
		SiteObjects 
	set 
		Name = @Name, Url=@Url, [Index]=@Index, IndexScope=@IndexScope, IndexByProperty=@IndexByProperty, IndexItems=@IndexItems, IndexId=@IndexId
	where 
		FarmID=@FarmID and WebApplicationID=@WebApplicationID and SiteID=@SiteID and WebID=@WebID and ObjectType=@ObjectType and ObjectID=@ObjectID
end else
begin
	insert into SiteObjects
		(FarmID, WebApplicationID, SiteID, WebID, ObjectID, Name, ObjectType, Url, [Index], IndexScope, IndexByProperty, IndexItems, IndexId)
	values
		(@FarmID, @WebApplicationID, @SiteID, @WebID, @ObjectID, @Name, @ObjectType, @Url, @Index, @IndexScope, @IndexByProperty, @IndexItems, @IndexId)
end

END
GO

CREATE TABLE [dbo].[ObjectReferences](
	[SiteID] [uniqueidentifier] NOT NULL,
	[WebID] [uniqueidentifier] NOT NULL,
	[ReferenceID] [nvarchar](512) NOT NULL,
	[ObjectID] [nvarchar](512) NOT NULL,
	[Name] [nvarchar](255) NOT NULL,
	[ReferenceType] [tinyint] NOT NULL,
	[Url] [nvarchar](255) NOT NULL,
	[SiteTitle] [nvarchar](255) NOT NULL,
	[SiteUrl] [nvarchar](255) NOT NULL,
	[ListTitle] [nvarchar](255) NULL,
	[ListUrl] [nvarchar](255) NULL
) ON [PRIMARY]

GO
-- =============================================
CREATE PROCEDURE [dbo].[DeleteObjectReferences]
	@SiteID UniqueIdentifier,
	@WebID UniqueIdentifier
AS
BEGIN
	delete
	from
		ObjectReferences
	where
		SiteID=@SiteID and WebID=@WebID
END
GO